using System;
using System.Data;
using System.Data.Odbc;
using System.Collections.Generic;
using System.Text;

namespace caseDrawer
{

	public sealed class Database
	{
		static Database instance = new Database();
		OdbcConnection cn;
		OdbcCommand cmd ;
		OdbcDataAdapter da;
		DataSet ds;

		public static Database Instance {
			get {
				return instance;
			}
		}

		private Database()
		{
			this.cn = getConnection();
		}

		OdbcConnection getConnection(){
			if(cn==null){
				string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
					"SERVER=localhost;" +
					"DATABASE=brickdb;" +
					"UID=root;" +
					"PASSWORD=9;";

				cn = new OdbcConnection(MyConString);
				cn.Open();
			}
			else if(cn.State== ConnectionState.Closed){
				cn.Open();
			}
			return cn;
		}

		public DataTable loadSenarios(){
			string sql = "select id, name from tbl_senario";
			return this.getResults(sql);
		}

		public DataTable loadBricks(Senario s){

			string sql = "select id,name,isRequired,hasInTimeValidation,accessEvent from tbl_brick where containerId = '" + s.Id + "'";

			return this.getResults(sql);

		}

		public OdbcDataReader loadResult(string brickId, char grp){
			string sql = "SELECT id, input ,output,inTimeValiTrigger from tbl_results where grp='"+ grp +"' AND containerId='" + brickId + "'";

			return this.getResult(sql);
		}
		
		public DataTable loadResults(IList<string> brickIds, char grp, string inputStr){
			StringBuilder sb = new StringBuilder();
			sb.Append("(");
			foreach(string id in brickIds){
				sb.Append("'");
				sb.Append(id);
				sb.Append("',");
			}
			sb.Append(")");
			int s = (sb.Length - 2);
			sb.Remove(s,1);
			string sql = "SELECT id, input ,output,inTimeValiTrigger from tbl_results where grp='"+ grp +"' AND "+ inputStr +" AND containerId in " + sb.ToString();

			return this.getResults(sql);
		}
		
		
		public DataTable loadResults(string brickId, char grp){
			string sql = "select id, input ,output,inTimeValiTrigger from tbl_results where grp='"+ grp +"' AND containerId='"+ brickId +"'";

			return this.getResults(sql);
		}

		public string saveSenario(Senario s){
			StringBuilder sb = new StringBuilder();
			string newId = s.Id;

			if(!isExist(s)){
				newId = this.getNewElementId(s);
				sb.Append("insert into tbl_senario ");
				sb.Append("(id,name) ");
				sb.Append("values('");
				sb.Append(newId);
				sb.Append("','");
				sb.Append(s.Name);
				sb.Append("')");
			}
			else{
				sb.Append("update tbl_senario set ");
				sb.Append("name='");
				sb.Append(s.Name);
				sb.Append("' where id='");
				sb.Append(s.Id);
				sb.Append("'");
			}
			string str = sb.ToString();

			executeCommand(sb.ToString());
			return newId;

		}

		public string saveBrick(Brick b){
			StringBuilder sb = new StringBuilder();
			string newId = b.Id;

			if(!isExist(b)){
				newId = this.getNewElementId(b);

				sb.Append("insert into tbl_brick ");
				sb.Append("(id,name, containerId,isRequired,hasInTimeValidation,accessEvent)  ");
				sb.Append("values('");
				sb.Append(newId);
				sb.Append("','");
				sb.Append(b.Name);
				sb.Append("','");
				sb.Append(b.ContainerId);
				sb.Append("',");
				sb.Append(b.IsRequired);
				sb.Append(",");
				sb.Append(b.HasInTimeValidataion);
				sb.Append(",'");
				sb.Append(b.AccessEvent);
				sb.Append("')");
			}
			else{
				sb.Append("update tbl_brick set ");
				sb.Append("name='");
				sb.Append(b.Name);
				sb.Append("',containerId='");
				sb.Append(b.ContainerId);
				sb.Append("',isRequired=");
				sb.Append(b.IsRequired);
				sb.Append(",hasInTimeValidation=");
				sb.Append(b.HasInTimeValidataion);
				sb.Append(",accessEvent='");
				sb.Append(b.AccessEvent);
				sb.Append("' where id='");
				sb.Append(b.Id);
				sb.Append("'");
			}
			string str = sb.ToString();
			executeCommand(sb.ToString());

			return newId;
		}

		public string saveResults(Result r){
			StringBuilder sb = new StringBuilder();
			string newId = r.Id;

			if(!isExist(r)){
				newId = this.getNewElementId(r);

				sb.Append("insert into tbl_results ");
				sb.Append("(id,input, output, inTimeValiTrigger, grp, containerId)  ");
				sb.Append("values('");
				sb.Append(newId);
				sb.Append("','");
				sb.Append(r.Input);
				sb.Append("','");
				sb.Append(r.Output);
				sb.Append("','");
				sb.Append(r.Trigger);
				sb.Append("','");
				sb.Append(r.GRP);
				sb.Append("','");
				sb.Append(r.ContainerId);
				sb.Append("')");
			}
			else{
				sb.Append("update tbl_results set ");
				sb.Append("input='");
				sb.Append(r.Input);
				sb.Append("',output='");
				sb.Append(r.Output);
				sb.Append("',inTimeValiTrigger='");
				sb.Append(r.Trigger);
				sb.Append("',grp='");
				sb.Append(r.GRP);
				sb.Append("',containerId='");
				sb.Append(r.ContainerId);
				sb.Append("' where id='");
				sb.Append(r.Id);
				sb.Append("'");
			}

			executeCommand(sb.ToString());

			return newId;
		}

		void executeCommand(string sql){
			cmd = new OdbcCommand(sql,this.getConnection());
			cmd.ExecuteNonQuery();
			cn.Close();
		}

		bool isExist(Element e){
			string sql="";

			if(e.GetType()== typeof(Brick))
				sql = "select count(id) as total from tbl_brick where id='" + e.Id + "'";
			if(e.GetType()== typeof(Senario))
				sql = "select count(id) as total from tbl_senario where id='" + e.Id + "'";

			return this.getCount(sql);
		}

		bool isExist(Result r){
			string sql = "select count(id) as total from tbl_results where id='" + r.Id +"'";

			return this.getCount(sql);
		}

		bool getCount(string str){
			bool find=true ;

			cmd = new OdbcCommand(str, this.getConnection());
			OdbcDataReader dr =cmd.ExecuteReader();

			if(dr.Read()){
				int ii = int.Parse(dr["total"].ToString());
				if(int.Parse(dr["total"].ToString())==0)
					find = false;
			}

			cn.Close();

			return find;
		}

		String getNewElementId(Element e){
			String sql;
			if(e.GetType()== typeof(Senario))
				sql = "select max(id) as maxid from tbl_senario";
			else if(e.GetType()== typeof(Brick))
				sql = "select max(id) as maxid from tbl_brick";
			else
				sql = "select max(id) as maxid from tbl_results";

			cmd = new OdbcCommand(sql, this.getConnection());
			OdbcDataReader dr =cmd.ExecuteReader();

			string maxId="";
			string newId="";

			if(dr.Read()){
				maxId = dr["maxid"].ToString() == ""? "0000" : dr["maxid"].ToString();

				StringBuilder sb = new StringBuilder("0000");

				int id = Convert.ToInt32(maxId) + 1;

				sb.Append(id);
				newId = sb.Remove(0,sb.Length - 4).ToString();

			}
			return newId;
		}

		OdbcDataReader getResult(string sql)
		{
			cmd = new OdbcCommand(sql, this.getConnection());
			OdbcDataReader reader = cmd.ExecuteReader();

			cn.Close();

			return reader;
		}

		DataTable getResults(string sql)
		{
			cmd = new OdbcCommand(sql, this.getConnection());
			da = new OdbcDataAdapter(cmd);
			ds = new DataSet();
			da.Fill(ds);

			cn.Close();

			return ds.Tables[0];
		}
	}
}
